package com.correo.database.server;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

import com.correo.database.beans.IBean;
import com.correo.database.beans.OfficeBean;
import com.correo.database.beans.UserBean;

public class ManagerDataBaseOffice implements IManagerDataBase{
	private Connection connection;
	public ManagerDataBaseOffice(Connection connection) {
		this.connection = connection;
	}
	public void alta(IBean dataBean) throws SQLException {
		String strQuery="{CALL usp_officeInsert(?,?,?,?,?,?)}";
		CallableStatement callStm=getConnection().prepareCall(strQuery);
		callStm.setString( 1,((OfficeBean)dataBean).getName());
		callStm.setDate( 2,new Date(Calendar.getInstance().getTimeInMillis()));
		callStm.setString( 3,null);//descripcion
		callStm.setString( 4,((OfficeBean)dataBean).getName());
		callStm.setString( 5,((OfficeBean)dataBean).getName());
		callStm.setString( 6,null);
		
		callStm.execute();
		getConnection().commit();
		callStm.close();
		if(((OfficeBean)dataBean).getRelationship()!=null){
			Iterator<OfficeBean> it=((OfficeBean)dataBean).getRelationship().iterator();
			OfficeBean relation=null;
			while(it.hasNext()){
				relation = it.next();
				strQuery="{CALL usp_relationshipInsert(?,?,?,?,?)}";
				callStm=getConnection().prepareCall(strQuery);
				callStm.setString( 1,((OfficeBean)dataBean).getName());
				callStm.setString( 2,relation.getName());
				callStm.setDate(3,new Date(Calendar.getInstance().getTimeInMillis()));//descripcion
				callStm.setString( 4,null);
				callStm.setString( 5,null);
				callStm.execute();
				getConnection().commit();
				callStm.close();				
			}
		}
	}
	public void modify(IBean dataBean) throws SQLException {
		String strQuery="{CALL usp_officeUpdate(?,?,?,?,?,?)}";
		CallableStatement callStm=getConnection().prepareCall(strQuery);
		callStm.setString( 1,((OfficeBean)dataBean).getName());
		callStm.setDate( 2,new Date(Calendar.getInstance().getTimeInMillis()));
		callStm.setString( 3,null);//descripcion
		callStm.setString( 4,((OfficeBean)dataBean).getName());
		callStm.setString( 5,((OfficeBean)dataBean).getName());
		callStm.setString( 6,null);
		
		callStm.execute();
		getConnection().commit();
		callStm.close();
		/////TODO BAJAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
		 strQuery="{CALL usp_relationshipDelete(?,?)}";
		 callStm = getConnection().prepareCall(strQuery);
		 callStm.setString(1, ((OfficeBean)dataBean).getName());
		 callStm.setString(2, null);
		 callStm.execute();
		 getConnection().commit();
		 callStm.close();
		 
		if(((OfficeBean)dataBean).getRelationship()!=null){
			Iterator<OfficeBean> it=((OfficeBean)dataBean).getRelationship().iterator();
			OfficeBean relation=null;
			while(it.hasNext()){
				relation = it.next();
				strQuery="{CALL usp_relationshipInsert(?,?,?,?,?)}";
				callStm=getConnection().prepareCall(strQuery);
				callStm.setString( 1,((OfficeBean)dataBean).getName());
				callStm.setString( 2,relation.getName());
				callStm.setDate(3,new Date(Calendar.getInstance().getTimeInMillis()));//descripcion
				callStm.setString( 4,null);
				callStm.setString( 5,null);
				callStm.execute();
				getConnection().commit();
				callStm.close();				
			}
		}
		
	}
	public List<IBean> refresh() throws SQLException {
		List<IBean> lista = new LinkedList<IBean>();
		String strQuery="{CALL usp_officeSelect(?)}";
		CallableStatement stm = getConnection().prepareCall(strQuery);
		stm.setString(1, null);
		ResultSet result = stm.executeQuery();
		OfficeBean office=null;
		while(result.next()){
			office = new OfficeBean();
			office.setName(result.getString("name"));			
			lista.add(office);
		}
		stm.close();
		
//busco las relaciones de confianza de cada oficcina
		
		
		Iterator<IBean> it=lista.iterator();
		OfficeBean relation=null;
		while(it.hasNext()){
			Set<OfficeBean> listaRelacion = new LinkedHashSet<OfficeBean>();
			office = (OfficeBean) it.next();
			strQuery="{CALL usp_relationshipSelect(?,?)}";
			stm = getConnection().prepareCall(strQuery);
			stm.setString(1, office.getName());
			stm.setString(2, null);
			ResultSet resultRelation = stm.executeQuery();
			while(resultRelation.next()){
				relation = new OfficeBean();
				relation.setName(resultRelation.getString("relationShipId"));			
				listaRelacion.add(relation);
			}
			office.setRelationship(listaRelacion);
			stm.close();
		}
		stm.close();
		
		return lista;
	}
	public void getSelection(OfficeBean office) throws SQLException {
		
		if(office!=null){
			
			
	//busco las relaciones de confianza de cada oficcina
			
			
			
			OfficeBean relation=null;
			
				Set<OfficeBean> listaRelacion = new LinkedHashSet<OfficeBean>();
				
				String strQuery="{CALL usp_relationshipSelect(?,?)}";
				CallableStatement stm = getConnection().prepareCall(strQuery);
				stm.setString(1, office.getName());
				stm.setString(2, null);
				ResultSet resultRelation = stm.executeQuery();
				while(resultRelation.next()){
					relation = new OfficeBean();
					relation.setName(resultRelation.getString("relationShipId"));			
					listaRelacion.add(relation);
				}
				office.setRelationship(listaRelacion);
				
			
			stm.close();
		}

	}
	public void remove(List<IBean> listBean) throws SQLException {
		if(listBean!=null){
			Iterator<IBean> it = listBean.iterator();
			String strQuery="";
			CallableStatement stm=null;
			OfficeBean officeBean=null;
			ResultSet result=null;
			while(it.hasNext()){
				 officeBean = (OfficeBean) it.next();
				 //Borro todas las relaciones
				 strQuery="{CALL usp_relationshipDelete(?,?)}";
				 stm = getConnection().prepareCall(strQuery);
				 stm.setString(1, officeBean.getName());
				 stm.setString(2, null);
				 stm.executeUpdate();
				 getConnection().commit();
				 stm.close();
				 
				//Borro todas las relaciones con las casillas
				 strQuery="{CALL usp_correoOfficeDelete(?,?)}";
				 stm = getConnection().prepareCall(strQuery);
				 stm.setString(1, null);
				 stm.setString(2, officeBean.getName());
				 stm.executeUpdate();
				 getConnection().commit();
				 stm.close();
				 
				 strQuery="{CALL usp_officeDelete(?)}";
				 stm = getConnection().prepareCall(strQuery);
				 stm.setString(1, officeBean.getName());
				 stm.executeUpdate();
				 getConnection().commit();
				 stm.close();
				 
				 
			}
		}
		
	}
	public Connection getConnection() {
		return connection;
	}

}
